package com.ghck.server.dao;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;

import com.ghck.server.dao.provider.DiseaseProvider;
import com.ghck.server.model.Disease;
import com.ghck.server.model.DiseaseCategoryDict;
import com.ghck.server.model.Symptom;
import com.ghck.server.model.query.DiseaseQuery;
import com.ghck.server.model.response.DiseaseRes;
import com.ghck.server.model.response.IdNameRes;
/**
 * 疾病库表  <br>
 * t_disease <br>
 * v1.0 2016-05-01
 * 
 * 逻辑删除，方便redis同步
 * 
 */
 public interface DiseaseMapper {
 	
 	/**
 	*根据id删除 疾病库表
 	*/
 	@Delete("delete from t_disease where id=#{id}")
 	public Integer deleteById(Integer id);
 	
 	/**
 	* 批量新增 疾病库表
 	*/
 	@InsertProvider(method="batchInsert",type=DiseaseProvider.class)
	@Options(useGeneratedKeys=true,keyColumn="id",keyProperty="list[0].id")
 	public Integer batchInsert(List<? extends Disease> diseases);
 	
 	/**
 	* 新增 疾病库表
 	*/
 	@InsertProvider(method="insert",type=DiseaseProvider.class)
	@Options(useGeneratedKeys=true)
 	public Integer insert(Disease domain);
 
 	
 	/**
 	*更新 疾病库表
 	*/
 	@UpdateProvider(method="update",type=DiseaseProvider.class)
 	public Integer update(Disease domain);
 	
 	/**
 	*根据ID查询 疾病库表
 	*/
	@Select("select * from t_disease where id=#{id}")
	public Disease findById(Integer id);
	
	/**
 	*通过query 查询 疾病库表
 	*/
 	@SelectProvider(method="findByQuery",type=DiseaseProvider.class)
 	public List<Disease> findByQuery(DiseaseQuery query);
 	
 	/**
 	*通过query 查询 疾病库表
 	*/
 	@SelectProvider(method="findByQuery",type=DiseaseProvider.class)
 	public List<DiseaseRes> findByQueryRes(DiseaseQuery query);
 	
 	/**
 	*通过query 查询 疾病库表
 	*/
 	@Select("select a.id,a.name,a.disease_desc,b.matching_rate from t_disease a,r_symptom_disease b "
 			+ " where a.id = b.disease_id and b.symptom_id = #{symptomId} order by b.matching_rate desc limit #{start},#{limit}")
 	public List<DiseaseRes> findDiseaseResByKeyword(DiseaseQuery query);
 	
 	/**
 	*通过query count总条数
 	*/
 	@SelectProvider(method="countByQuery",type=DiseaseProvider.class)
 	public Integer countByQuery(DiseaseQuery query);
 	
 	/**
 	* 通过ids查询
 	*/
 	@SelectProvider(method="findByIds",type=DiseaseProvider.class)
 	public List<Disease> findByIds(List<Integer> ids);
 	
 	@Select("select t.id,t.name from r_disease_category r ,t_disease t where disease_id = t.id and r.cat_id = #{categoryId}")
 	public List<DiseaseCategoryDict> getDiseaseByCategoryId(Integer categoryId);
 	
 	@Select("select body_part_id from r_disease_bodypart where disease_id = #{diseaseId}")
 	public List<Integer> getDiseaseBybodypartIds(Integer diseaseId);
 	/**
 	 * 根据身体部位获取疾病列表
 	 * @param bodypartId
 	 * @return
 	 */
 	@Select("select a.id,a.name from t_disease a left join r_disease_bodypart b on a.id = b.disease_id where b.body_part_id = #{bodypartId}")
 	public List<IdNameRes> findDiseaseByBodypartId(Integer bodypartId);
 	
 	@Select("select * from t_disease where in_used=1")
 	public List<Disease> findAll();
 	
 	@Select("select * from t_disease where name=#{name} limit 1")
 	public Disease findByName(String name);
 	
 	@Select("select a.id,a.name from t_symptom a,r_bodypart_symptom b where a.id = b.symptom_id and b.bodypart_id in "
 			+ " (select c.id from dict_bodypart c where (c.id = #{bodypartId} or c.parent_id = #{bodypartId}) )")
 	public List<Symptom> findSymptonListByBodypartId(Integer bodypartId);
 	
 	@Select("select a.id,a.name,a.disease_desc,b.matching_rate from t_disease a,r_symptom_disease b "
 			+ " where a.in_used = 1 and a.id = b.disease_id and b.symptom_id = #{symptomId} order by b.matching_rate limit #{start},#{limit}")
 	public List<DiseaseRes> findDiseaseListForSymptomId(DiseaseQuery query);
 	
 	
// 	@Select("select a.id,max(a.name) name,max(a.disease_desc) disease_desc,max(b.matching_rate) matching_rate "
// 			+ " from t_disease a,r_symptom_disease b where a.id = b.disease_id and a.name like concat('%',#{keyword},'%') "
// 			+ " group by a.id order by matching_rate desc limit #{start},#{limit}")
 	
 	@Select("select a.id,max(a.name) name,max(a.disease_desc) disease_desc,max(c.matching_rate) matching_rate "
 			+ "from t_disease a , (select a.id from t_disease a , r_symptom_disease b where a.id = b.disease_id "
 			+ "and a.name like concat('%',#{keyword},'%') union select disease_id id from t_symptom a,"
 			+ "r_symptom_disease b where a.id = b.symptom_id and a.name  like concat('%',#{keyword},'%') "
 			+ "group by b.disease_id ) b ,r_symptom_disease c where a.id = b.id and a.id = c.disease_id "
 			+ "group by a.id  order by matching_rate desc limit #{start},#{limit}")
 	public List<DiseaseRes> findDiseaseListForKeyword(DiseaseQuery query);
 	

}	